package com.partner.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;




import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.partner.domain.DianZanCollect;
import com.partner.domain.TieZi;
import com.partner.domain.TieZiMessage;
import com.partner.domain.TieZiReply;
import com.partner.response.TieZiResponse;
import com.partner.util.DBUtil;
import com.partner.util.DateUtil;

/**
 * 有关帖子的dao
 * @author Jinshuan
 *
 */
public class TieZiDao {
	QueryRunner queryRunner_auto = new QueryRunner(DBUtil.getDataSource());
	QueryRunner queryRunner_self = new QueryRunner();
	//查询帖子，按评论数排列
	public TieZiResponse getTieZi(int pageNow,int pageSize){
		Connection connection = null;
		try {
			connection =  DBUtil.getConnection();
			connection.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
//		String sql = "select tiezi.*,user.photo,user.username from user,tiezi where tiezi.phone = user.phone order by  comment_num DESC limit ?,?";
		String sql = "select tb.*,user.photo,user.username from user,(SELECT * from tiezi as tb ORDER BY tb.comment_num desc ) tb where tb.phone = user.phone limit ?,?";
		Object[] params = {(pageNow-1)*pageSize,pageSize};
		

		TieZiResponse tieZiResponse = new TieZiResponse();
		List<TieZi> tiezis = new ArrayList<TieZi>();
		try {
			List<TieZi> temp = queryRunner_self.query(connection,sql, new BeanListHandler<TieZi>(TieZi.class), params);
			if(temp!=null){
				tiezis = temp;
				
				//查询完这个再去查询对应的评论
				 String sql2 = "";
				 boolean flag = true;
				 for(TieZi tiezi:tiezis){
					 List<TieZiReply> replys = new ArrayList<TieZiReply>();
					 sql2 = "select tiezi_reply.*,user.photo,user.username from user,tiezi_reply  where tiezi_reply.phone = user.phone and tie_id = ? and tiezi_reply.isdelete = 0 order by tiezi_reply.time desc";
					 Object[] params2 = {tiezi.getId()};
					 List<TieZiReply> temp2 = queryRunner_self.query(connection, sql2, new BeanListHandler<TieZiReply>(TieZiReply.class),params2);
					 if(temp2!=null){
						 replys = temp2;
					 }else{
						 flag = false;
						 tieZiResponse.setCode("400");
						 tieZiResponse.setDescription("查询失败");
						 return tieZiResponse;
					 }
					 tiezi.setReplys(replys);
				 }
				
				tieZiResponse.setCode("2000");
				tieZiResponse.setTiezis(tiezis);
			}
			//关闭资源
			DbUtils.commitAndCloseQuietly(connection);
		} catch (SQLException e) {
			tieZiResponse.setCode("400");
			tieZiResponse.setDescription("查询失败，稍后再试");
			tieZiResponse.setTiezis(new ArrayList<TieZi>());
			DbUtils.rollbackAndCloseQuietly(connection);
		}
		
		return tieZiResponse;
	}
	
	//增加一个帖子的回复
	public int insertReply(TieZiReply tieZiReply){//把帖子的楼数给返回去
//		int loushu = 0;
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		boolean result = false;
		//先将楼数得到
		int loushu = getTieZiLouShu(tieZiReply.getTie_id());
		//增加到回复表中
		String sql = "insert into tiezi_reply(tie_id,phone,reply_loushu,content,loushu,time,huifu_phone) values(?,?,?,?,?,?,?)";
		String time = DateUtil.dateToString3(new Date());
		if(tieZiReply.getTime()!=null){
			time = tieZiReply.getTime();
		}
		Object[] params = {tieZiReply.getTie_id(),tieZiReply.getPhone(),tieZiReply.getReply_loushu(),tieZiReply.getContent(),loushu+1,time,tieZiReply.getHuifu_phone()};
		
		//给帖子表的评论数增加1
		String sql2 = "update tiezi set comment_num = comment_num+1 where id=?";
		Object[] params2 = {tieZiReply.getTie_id()};
		
		try {
			if(queryRunner_self.update(conn,sql, params)>0){
				if(queryRunner_self.update(conn, sql2, params2)>0){
					result = true;
				}
			}
			
			DbUtils.commitAndCloseQuietly(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("插入结果--"+result);
		return loushu;
		
	}
	
	//查询对应的帖子现在回复的最大楼数是多少
	public int getTieZiLouShu(int tiezi_id){
		String sql = "select max(loushu) from tiezi_reply where tie_id = ?";
		Object[] params = {tiezi_id};
		int loushu = 0;
		try {
			Integer temp = (Integer) queryRunner_auto.query(sql, new ScalarHandler(1), params);
			if(temp!=null){
				
				loushu = temp;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return loushu;
	}
	
	//将对应用户的点赞和收藏情况查询出来
	public List<DianZanCollect> getDianzan(String phone){
		List<DianZanCollect> dianZanCollects = new ArrayList<DianZanCollect>();
		String sql = "select * from tiezi_zan_collect where phone = ?";
		Object[] params = {phone};
		try {
			List<DianZanCollect> temp = queryRunner_auto.query(sql, new BeanListHandler<DianZanCollect>(DianZanCollect.class), params);
			if(temp!=null){
				
				dianZanCollects = 	temp;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dianZanCollects;
	}
	
	//将对应的点赞记录给删除
	public boolean deleteZan(DianZanCollect dianZanCollect){
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql = "delete from tiezi_zan_collect where id = ?";
		Object[] params = {dianZanCollect.getId()};
		try {
			if(queryRunner_self.update(conn,sql, params)>0){
				//然后去给对应的表中的赞数或者评论数减1
				String sql2 = "";
				if(dianZanCollect.getType_tie()==0&&dianZanCollect.getType_zan()==0){//帖子点赞
					sql2 = "update tiezi set zan = zan-1 where id = ?";
				}else if(dianZanCollect.getType_tie()==0&&dianZanCollect.getType_zan()==1){//帖子收藏
					sql2 = "update tiezi set collect = collect-1 where id = ?";
				}else if(dianZanCollect.getType_tie()==1&&dianZanCollect.getType_zan()==0){//评论点赞
					sql2 = "update tiezi_reply set zan = zan-1 where id = ?";
				}
				Object param = dianZanCollect.getRef_id();
				
				queryRunner_self.update(conn, sql2, param);
				DbUtils.commitAndCloseQuietly(conn);
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return false;
	}
	//删除一条评论
		public boolean deleteReply(int id){
			String sql = "update tiezi_reply set isdelete = 1 where id = ?";
			Object[] param = {id};
			try {
				if(queryRunner_auto.update(sql, param)>0){
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}
	//增加一条点赞记录，增加时需要将对应的帖子或者评论的咱或者收藏数加1
	public int insertZanOrCollect(DianZanCollect dianZanCollect){
		Connection conn = null;
//		boolean result = false;
		int id = -1;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		String sql = "insert into tiezi_zan_collect(ref_id,phone,type_tie,type_zan,zan_shoucang_time ) values(?,?,?,?,?)";
		String shoucang_dianzan_time = dianZanCollect.getZan_shoucang_time();
		if(shoucang_dianzan_time==null){
			shoucang_dianzan_time = DateUtil.dateToString3(new Date());
		}
		Object[] params = {dianZanCollect.getRef_id(),dianZanCollect.getPhone(),dianZanCollect.getType_tie(),dianZanCollect.getType_zan(),shoucang_dianzan_time};
		Object[] params2 = {dianZanCollect.getRef_id(),dianZanCollect.getPhone(),dianZanCollect.getType_tie(),dianZanCollect.getType_zan()};
		try {
				if(queryRunner_self.update(conn, sql, params)>0){
					
					String sql2 = "select id from tiezi_zan_collect where ref_id = ? and phone = ? and type_tie = ? and type_zan = ?";
					Integer temp = (Integer) queryRunner_self.query(conn, sql2, new ScalarHandler(1), params2);
					if(temp!=null){
						id = temp;
					}
					//将对应的帖子或者评论的点赞数或者是收藏数加1
					String sql3 = "";
					if(dianZanCollect.getType_tie()==0&&dianZanCollect.getType_zan()==0){//帖子点赞
						
						sql3 = "update tiezi set zan = zan+1 where id = ?";
					}else if(dianZanCollect.getType_tie()==0&&dianZanCollect.getType_zan()==1){//帖子收藏
						sql3 = "update tiezi set collect = collect+1 where id = ?";
					}else if(dianZanCollect.getType_tie()==1&&dianZanCollect.getType_zan()==0){//评论点赞
						sql3 = "update tiezi_reply set zan = zan+1 where id = ?";
					}
					Object param = dianZanCollect.getRef_id();
					queryRunner_self.update(conn, sql3, param);
					DbUtils.commitAndCloseQuietly(conn);
					return id;
				}
				
		} catch (SQLException e) {
			id = -1;
			e.printStackTrace();
		}
		
		return id;
	}
	
//查询对应用户的收藏帖子
	public List<TieZi> selectShouCangTieZiForUser(String phone,int pageNow,int pageSize){
		Connection connection = null;
		try {
			connection =  DBUtil.getConnection();
			connection.setAutoCommit(false);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		String sql = "select tiezi.*,user.photo,user.username from tiezi,tiezi_zan_collect,user where tiezi_zan_collect.phone = ? and "+
" tiezi.id = tiezi_zan_collect.ref_id and tiezi_zan_collect.type_zan = 1 and tiezi_zan_collect.type_tie = 0 and user.phone = tiezi.phone "+
" ORDER BY tiezi_zan_collect.zan_shoucang_time asc limit ?,?";
		Object[] params = {phone,(pageNow-1)*pageSize,pageSize};
		
		List<TieZi> tiezis;
		try {
			tiezis = queryRunner_self.query(connection, sql, new BeanListHandler<TieZi>(TieZi.class), params);
//			tiezis = queryRunner_auto.query(sql, new BeanListHandler<TieZi>(TieZi.class), params);
			if(tiezis!=null){
//				String sql = 
				//查询完这个再去查询对应的评论
//				 String sql2 = "";
//				 boolean flag = true;
				 String sql2 = "select tiezi_reply.*,user.photo,user.username from user,tiezi_reply  where tiezi_reply.phone = user.phone and tie_id = ? and tiezi_reply.isdelete = 0 order by tiezi_reply.time desc";
				for(TieZi tiezi:tiezis){
					 List<TieZiReply> replys = new ArrayList<TieZiReply>();
					 Object[] params2 = {tiezi.getId()};
					 List<TieZiReply> temp2 = queryRunner_self.query(connection, sql2, new BeanListHandler<TieZiReply>(TieZiReply.class),params2);
					 if(temp2!=null){
						 replys = temp2;
					 }
					 tiezi.setReplys(replys);
				}
				DbUtils.commitAndCloseQuietly(connection);
				return tiezis;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	//查询对应用户发出的帖子
	public List<TieZi> getTieZiForUser(String phone,int pageNow,int pageSize){
		String sql = "select tiezi.*,user.photo,user.username from user,tiezi where tiezi.phone = user.phone and  tiezi.phone = ? order by  time DESC limit ?,?";
		Object[] params = {phone,(pageNow-1)*pageSize,pageSize};
		System.out.println("phone--"+phone+"pageNow"+(pageNow-1)*pageSize+"pagesize--"+pageSize);
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		List<TieZi> tieZis = null ;
		try {
			tieZis = queryRunner_self.query(conn, sql, new BeanListHandler<TieZi>(TieZi.class), params);
			if(tieZis!=null){
				for(TieZi tiezi:tieZis){
//					String sql2 = "select * from tiezi_reply where tie_id = ? and phone = ?";
					String sql2 = "select tiezi_reply.*,user.photo,user.username from user,tiezi_reply  where tiezi_reply.phone = user.phone and tiezi_reply.phone =? and tie_id = ? and tiezi_reply.isdelete = 0 order by tiezi_reply.time desc";
					Object params2[] = {phone,tiezi.getId()};
					List<TieZiReply> tieZiReplies = queryRunner_self.query(conn, sql2, new BeanListHandler<TieZiReply>(TieZiReply.class), params2);
					if(tieZiReplies!=null){
						tiezi.setReplys(tieZiReplies);
					}
					
					DbUtils.commitAndCloseQuietly(conn);
				}
			}else{
				tieZis = new ArrayList<TieZi>();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tieZis;
	}
	
	//像数据表中插入数据
	public boolean addTieZi(String phone,String content,String time,String img){
		boolean result = false;
		String sql = "insert into tiezi(content,phone,time,img) VALUES(?,?,?,?)";
		Object[] params = {content,phone,time,img};
		try {
			if(queryRunner_auto.update(sql, params)>0){
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		}
		return result;
	}
	
	//查询关于对应用户的帖子消息
	public List<TieZiMessage> getTieZiMessages(String phone,int pageNow,int pageSize){
		String sql = "select tiezi_reply.phone as fromWho,tiezi_reply.loushu,tiezi_reply.content,tiezi_reply.time,`user`.username as reply_username,`user`.photo as reply_photo,tiezi.content as yuantie_context,tiezi.id as tiezi_id from  tiezi_reply,tiezi,`user`  where `user`.phone = tiezi_reply.phone and tiezi_reply.louzhu_state =0 and  tiezi_reply.tie_id = tiezi.id and tiezi.phone = ? and tiezi_reply.phone!=? and tiezi_reply.isdelete = 0 limit ?,?";
		Object[] params = {phone,phone,(pageNow-1)*pageSize,pageSize};
		List<TieZiMessage> tieZiMessages = new ArrayList<TieZiMessage>();
		try {
			tieZiMessages = queryRunner_auto.query(sql, new BeanListHandler<TieZiMessage>(TieZiMessage.class), params);
			for(TieZiMessage tieZiMessage:tieZiMessages){
				tieZiMessage.setIsLouzhu(0);
				//这个是楼主的帖子，所以原帖username直接就是该主人的
			}
			String sql2 = "select tiezi_reply.phone as fromWho,tiezi_reply.loushu,tiezi_reply.content,tiezi_reply.time,`user`.username as reply_username,`user`.photo,tiezi.content  as yuantie_context ,tiezi.id as tiezi_id from tiezi_reply,`user`,tiezi where  tiezi.id = tiezi_reply.tie_id and `user`.phone =tiezi_reply.phone and  huifuloushu_state = 0 and tiezi_reply.huifu_phone = ? and tiezi_reply.phone!=? and tiezi_reply.isdelete = 0 limit ?,?";
			List<TieZiMessage> tieZiMessages2 = queryRunner_auto.query(sql2, new BeanListHandler<TieZiMessage>(TieZiMessage.class), params);
			for(TieZiMessage tieZiMessage:tieZiMessages2){
				tieZiMessage.setIsLouzhu(1);
			}
			tieZiMessages.addAll(tieZiMessages2);
			//将原帖的用户名字查出来
			String sql3 = "select `user`.username,`user`.photo from user,tiezi where tiezi.phone = user.phone and tiezi.id= ?";
			for(TieZiMessage tieZiMessage:tieZiMessages){
				Object[] params3 = {tieZiMessage.getTiezi_id()};
				String yuantie_username = (String) queryRunner_auto.query(sql3, new ScalarHandler(1), params3);
				String yuantie_photo = (String) queryRunner_auto.query(sql3, new ScalarHandler(2), params3);
				tieZiMessage.setYuantie_username(yuantie_username);
				tieZiMessage.setYuantie_img(yuantie_photo);
			}
			
		} catch (SQLException e) {
			if(tieZiMessages!=null){
				tieZiMessages = new ArrayList<TieZiMessage>();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tieZiMessages;
		
	}
}
